SCRIPT: Update SQL Database From an Access Saved Query
Here we will go through a VB script that reades the result of a saved query created on a Microsoft Access Database VDM_VacationApproval: The saved query in Access DB. SQL 2005 case: (You may need to install SQL 2005 Native Client (32 bit 64 bit) to be able to use ODBC Driver for SQL 2005) 'On Error Resume next '################ '#Access Portion# '################ Dim connection_string1 : connection_string1 = _ "provider=microsoft.jet.oledb.4.0;" _ & "data source=\\fileserver\Departments\HR\HR\HR.mdb" Dim conn1 : Set conn1 = createobject("adodb.connection") conn1.open connection_string1 strACCQuery = "SELECT * FROM VDM_VacationApproval" Set rs1 = createobject("adodb.recordset") rs1.Open strACCQuery, conn1, 3, 3 '############# '#SQL Portion# '############# 'Delete the table content Dim conn2 : Set conn2 = createobject("adodb.connection") Dim connection_string2 : connection_string2 = ("Driver={SQL Native Client};Server=SQL01;Database=HRDB;Uid=hruser;Pwd=hrpassword;") conn2.open connection_string2 strSQLQuery = "DELETE FROM HRtable" Set rs2 = createobject("adodb.recordset") rs2.Open strSQLQuery, conn2, 3, 3 '''Add the data collected from the access DB Dim col1, col2, col3, col4, col5, col6, col7, col8 While Not RS1.EOF col1 = rs1("EmployeeName") col2 = rs1("department") col3 = rs1("Date") col4 = rs1("vacation") col5 = rs1("manager") col6 = rs1("ImmediateManagerApproval") col7 = rs1("employeeID") col8 = rs1("ImmediateManagerDisapproval") '''To handle special case when a value in col5 is null If col5 <> "" Then col5 = REPLACE(col5,"'","") 'To handle mapping of access TRUE/FALSE to SQL 1/0 If col6 = "TRUE" Then col6 = 1 Else col6 = 0 If col8 = "TRUE" Then col8 = 1 Else col8 = 0 'WScript.Echo col1 & col2 & col3 & col4 & col5 &col6 & col7 & col8 strSQLQuery2 = "INSERT INTO HRtable VALUES ('" & _ col1 & "', '" & col2 & "', '"& col3 &"' , '" & col4 & "', '" & col5 & "', '" & col6 & "', '" & col7 & "', '" & col8 & "')" Set rs3 = CreateObject("ADODB.Recordset") rs3.Open strSQLQuery2, conn2, 3, 3 RS1.MoveNext Wend conn1.close Set conn1 = Nothing conn2.close Set conn2 = Nothing SQL 2008 case: (You may need to install SQL 2008 Native Client (32 bit 64 bit) to be able to use ODBC Driver for SQL 2008) 'On Error Resume next '################ '#Access Portion# '################ Dim connection_string1 : connection_string1 = _ "provider=microsoft.jet.oledb.4.0;" _ & "data source=\\fileserver\Departments\HR\HR\HR.mdb" Dim conn1 : Set conn1 = createobject("adodb.connection") conn1.open connection_string1 strACCQuery = "SELECT * FROM VDM_VacationApproval" Set rs1 = createobject("adodb.recordset") rs1.Open strACCQuery, conn1, 3, 3 '############# '#SQL Portion# '############# '''Delete the table content Dim conn2 : Set conn2 = createobject("adodb.connection") Dim connection_string2 : connection_string2 = ("Provider=SQLNCLI10;Data Source=tcp:GB-PSC-HYPERV\PSCSQL;User ID=HRuser;Password=hrpassword;Initial Catalog=HRDB;") conn2.open connection_string2 strSQLQuery = "DELETE FROM HRtable" Set rs2 = createobject("adodb.recordset") rs2.Open strSQLQuery, conn2, 3, 3 '''Add the data collected from the access DB Dim col1, col2, col3, col4, col5, col6, col7, col8 While Not RS1.EOF col1 = rs1("EmployeeName") col2 = rs1("department") col3 = rs1("Date") col4 = rs1("vacation") col5 = rs1("manager") col6 = rs1("ImmediateManagerApproval") col7 = rs1("employeeID") col8 = rs1("ImmediateManagerDisapproval") '''To handle special case when a value in col5 is null If col5 <> "" Then col5 = REPLACE(col5,"'","") '''To handle mapping of access TRUE/FALSE to SQL 1/0 If col6 = "TRUE" Then col6 = 1 Else col6 = 0 If col8 = "TRUE" Then col8 = 1 Else col8 = 0 'WScript.Echo col1 & col2 & col3 & col4 & col5 &col6 & col7 & col8 strSQLQuery2 = "INSERT INTO HRtable VALUES ('" & _ col1 & "', '" & col2 & "', '"& col3 &"' , '" & col4 & "', '" & col5 & "', '" & col6 & "', '" & col7 & "', '" & col8 & "')" Set rs3 = CreateObject("ADODB.Recordset") rs3.Open strSQLQuery2, conn2, 3, 3 RS1.MoveNext Wend conn1.close Set conn1 = Nothing conn2.close Set conn2 = Nothing Category:VBS Category:SQL Category:Access Category:Database Category:Script